package com.control;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.db.DBConnect;
import com.model.Post;

public class PostControl {
	
	/**
	 * 插入公告
	 * @param post
	 * @return
	 */
	public boolean insertPost(Post post) {
		DBConnect dbc = new DBConnect();
		String sql="{call insertPost("+post.getAdmin_id()+"," +
				"'"+post.getPost_content()+"')}";
		/*String sql = "Insert into post(admin_id, post_content, post_time) " +
					"values("+ post.getAdmin_id() +", '"+ post.getPost_content() +"', getDate())";
		*/
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			stmt.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			dbc.close();
		}
	}
	
	/**
	 * 删除公告
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public boolean deletePost(int id) throws SQLException {
		DBConnect dbc = new DBConnect();
		String sql="{call deletePost("+id+")}";
		/*
		 * String sql = "Delete from post where post_id = "+ id +" ";
		 */
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			stmt.execute();
			return true;
		} finally {
			dbc.close();	
		}
	}
	
	/**
	 * 更新公告
	 * @param post
	 * @return
	 * @throws SQLException
	 */
	public boolean updatePost(Post post) throws SQLException {
		DBConnect dbc = new DBConnect();
		String sql="{call updatePost("+post.getAdmin_id()+"," +
				"'"+post.getPost_content()+"',"+post.getPost_id()+")}";
		/*String sql = "update post set admin_id = '"+ post.getAdmin_id() +"', " +
				"post_time = getDate(), " +
				"post_content = '"+ post.getPost_content() +"' " +
				"where post_id = '"+ post.getPost_id() +"' ";
		*/
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			stmt.execute();
			return true;
		}finally {
			dbc.close();	
		}
	}
	
	/**
	 * 根据post_id获取公告信息
	 * @param post_id
	 * @return
	 */
	public String getPostContent(int post_id) {
		DBConnect dbc = new DBConnect();
		String sql="{call getPostContent("+post_id+")}";
		/*
		 * String sql = "select post_content from post where post_id = '"+ post_id +"'";
		 */
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			ResultSet rs = stmt.executeQuery();
			if(rs.next()) {
				return rs.getString("post_content");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
		return null;
	}
	
	/**
	 * 获取所有公告信息
	 * @return
	 */
	public ArrayList<Post> getPostInformation() {
		DBConnect dbc = new DBConnect();
		ArrayList<Post> postlist = new ArrayList<Post>();
		String sql="{call getPostInformation()}";
		/*String sql = "select * from post,admin where " +
				"post.admin_id = admin.admin_id";
		*/
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			ResultSet rs = stmt.executeQuery();
			while(rs.next()) {
				Post post = new Post();
				post.setPost_id(rs.getInt("post_id"));
				post.setAdmin_name(rs.getString("admin_name"));
				post.setPost_content(rs.getString("post_content"));
				String str = rs.getString("post_time");
				if(str != null)
					post.setPost_time(str.substring(0, 16));
				postlist.add(post);
			}
			return postlist;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
	}
}
